<!DOCTYPE html>
<html>
<head>
    <title>011.T-SQL学习</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图[未整理].html" class="">010.事务-索引-视图[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL[未整理].html" class="selected">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程[未整理].html" class="">012.存储过程[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器[未整理].html" class="">013.触发器[未整理]</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 011.T-SQL学习 </h1>
            <div class="doc_post"> <p class="md_block md_has_block_below md_has_block_below_ul">

<div class="toc"><ul>
<li>
<a href="#toc_0">011.T-SQL学习</a>
<ul>
<li>
<a href="#toc_1">1、T-SQL概述</a>
</li>
<li>
<a href="#toc_2">2、数据类型</a>
<ul>
<li>
<a href="#toc_3">①系统数据类型</a>
<ul>
<li>
<a href="#toc_4">(1)精确数字数据类型</a>
</li>
<li>
<a href="#toc_5">(2)近似数字数据类型</a>
</li>
<li>
<a href="#toc_6">(3)货币数据类型</a>
</li>
<li>
<a href="#toc_7">(4)字符数据类型</a>
</li>
<li>
<a href="#toc_8">(3)日期和时间数据类型</a>
</li>
<li>
<a href="#toc_9">(4)二进制数据类型</a>
</li>
<li>
<a href="#toc_10">(5)专用数据类型</a>
</li>
</ul>
</li>
<li>
<a href="#toc_11">②程序中的数据类型</a>
<ul>
<li>
<a href="#toc_12">(1)cursor</a>
</li>
<li>
<a href="#toc_13">(2)table</a>
</li>
<li>
<a href="#toc_14">　(3)sql_varint</a>
</li>
</ul>
</li>
</ul>
</li>
<li>
<a href="#toc_15">3、表达式</a>
<ul>
<li>
<a href="#toc_16">1.变量</a>
<ul>
<li>
<a href="#toc_17">(1)局部变量</a>
</li>
<li>
<a href="#toc_18">(2)全局变量</a>
</li>
</ul>
</li>
<li>
<a href="#toc_19">2.函数</a>
</li>
<li>
<a href="#toc_20">3.运算符</a>
</li>
<li>
<a href="#toc_21">4.流程控制语句</a>
<ul>
<li>
<a href="#toc_22">1.程序块语句BEGIN...END</a>
</li>
<li>
<a href="#toc_23">2.判断语句IF...ELSE</a>
</li>
<li>
<a href="#toc_24">3.循环语句WHILE</a>
</li>
<li>
<a href="#toc_25">4、分支判断语句CASE</a>
</li>
<li>
<a href="#toc_26">5.无条件退出语句RETURN</a>
</li>
<li>
<a href="#toc_27">6.无条件跳转语句GOTO</a>
</li>
<li>
<a href="#toc_28">7.延期执行语句WAITFOR　　</a>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">1、T-SQL概述</h2>

<p class="md_block">
    <span class="md_line">SQL Server用于操作数据库的编程语言为Transaction-SQL，简称T-SQL。T-SQL与PL/SQL不同，并没有固定的程序结构。<br /></span>
    <span class="md_line">  T-SQL包括以下4个部分：</span>
</p>


<ul>
<li>DDL：定义和管理数据库及其对象，例如create、alter和drop等。</li>
<li>DML：实现对数据库表各对象的操作，例如insert、update等。</li>
<li>DCL：数据控制语言，实现对数据库进行安全管理和权限管理等控制，例如grant、revoke、deny等。</li>
<li>附加的语言元素。T-SQL的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等。</li>
</ul>

<blockquote>

<p class="md_block">
    <span class="md_line">在T-SQL中，命令和语句的书写是不区分大小写的。</span>
</p>

</blockquote>

<h2 id="toc_2">2、数据类型</h2>

<p class="md_block">
    <span class="md_line">在SQL Server中提供了多种系统数据类型。除了系统数据类型外，还可以自定义数据类型。</span>
</p>


<h3 id="toc_3">①系统数据类型</h3>

<h4 id="toc_4">(1)精确数字数据类型</h4>

<p class="md_block">
    <span class="md_line">int<br /></span>
    <span class="md_line">  存储整型数值，存储数值范围为-231~231-1。<br /></span>
    <span class="md_line">bigint<br /></span>
    <span class="md_line">  bigint比int能存储更大的数值，存储数值范围为-263~263-1。<br /></span>
    <span class="md_line">smallint<br /></span>
    <span class="md_line">  数据类型的范围数值比int更小，在-215~215-1之间。定义这种数据类型的时候一定要小心，要确定存储的数据不会超过smallint所能存储的数值范围。<br /></span>
    <span class="md_line">tinyint<br /></span>
    <span class="md_line">  数据类型的范围数值比smallint更小，存储从 0 到 255 的整型数据。<br /></span>
    <span class="md_line">decimal/numeric<br /></span>
    <span class="md_line">  decimal[(p,s)]和numeric[(p,s)]这两种数据类型用于存储相同精度和范围的数据(小数点的左、右两边存储的数值位数相同)，所能存储的数值范围为-1038+1~1038-1。<br /></span>
    <span class="md_line">  p表示指定小数点左边和右边可以存储的十进制数字的最大个数，s指定小数位数。[(p,s)]的范围为1≤p≤38,0≤s≤p。若省略s，则默认为0；若未附带p及s，则numeric表示numeric(18)，只能表示整数</span>
</p>


<h4 id="toc_5">(2)近似数字数据类型</h4>

<p class="md_block">
    <span class="md_line">float<br /></span>
    <span class="md_line">  存储小数点不固定的数值，存储的数值范围。为-1.79E+308~1.79E+308。<br /></span>
    <span class="md_line">real<br /></span>
    <span class="md_line">  与float非常相似，存储数值范围为-3.40E+38~3.40E+38。</span>
</p>


<h4 id="toc_6">(3)货币数据类型</h4>

<p class="md_block">
    <span class="md_line">money<br /></span>
    <span class="md_line">smallmoney</span>
</p>


<h4 id="toc_7">(4)字符数据类型</h4>

<p class="md_block">
    <span class="md_line">char<br /></span>
    <span class="md_line">  长度固定，最多可以定义8000个字符。如果定义一个列为char(n)，则将存储n个字符。当输入少于定义的字符数时，剩余的长度将被在右边的空格填满。<br /></span>
    <span class="md_line">nchar<br /></span>
    <span class="md_line">  与char类型相似，但最多可以定义4000个字符，使用Unicode编码。<br /></span>
    <span class="md_line">varchar<br /></span>
    <span class="md_line">  与char一样，用于存储字母数字数据，最多可定义8000个字符。二者不同之处在于varchar的每一行可以有不同的字符数，最大字符数未定义的最大长度。例如列定义为varchar(50)，则该列数据最多可以有50个字符长。然而，如果列中只存储了3个字符长的字符串，则只会使用3个字符的存储空间。如果定义列时没有指定大小，即varchar()，则其长度默认为1。<br /></span>
    <span class="md_line">  特别，varchar(max)可以定义超过8000个字符的字符串数据类型，最多1073741824个字符。【注意不是varchar(n)】<br /></span>
    <span class="md_line">nvarchar<br /></span>
    <span class="md_line">  定义方式与varchar相似，除了nvarchar使用Unicode格式存储字符。</span>
</p>


<h4 id="toc_8">(3)日期和时间数据类型</h4>

<p class="md_block">
    <span class="md_line">date<br /></span>
    <span class="md_line">  仅用来存储日期，其范围从0001年1月1日到9999年12月31日。date数据类型的格式是YYYY-MM-DD。<br /></span>
    <span class="md_line">time<br /></span>
    <span class="md_line">  只存储基于24小时制的时间，其格式为hh:mm:ss[.nnnnnnn]。与date数据类型类似，为了给要存储在列中的数据提供准确的数据类型，能存储精确度达100纳秒的数据。<br /></span>
    <span class="md_line">datetime<br /></span>
    <span class="md_line">  用于存储从1753年1月1日到9999年12月31日之间的任何日期和时间。datetime不仅存储日期，而且会在日期的旁边存储时间。如果只像定义为datetime的列存入日期，则会在存储的日期中加入默认的时间12:00:00。<br /></span>
    <span class="md_line">datetime2<br /></span>
    <span class="md_line">  与datetime类似，datetime2用于存储日期和时间。不同之处是，datatime2的数据类型秒的小数部分的精度更高。此外，该数据类型能存储从0001年1月1日到9999年12月31日的日期。其格式为YYYY-MM-DD hh:mm:ss[.nnnnnnn]。<br /></span>
    <span class="md_line">smalldatetime<br /></span>
    <span class="md_line">  与datetime十分相似，除了smalldatetime可存储的数值范围是从1900年1月1日到2079年6月6日。该数值范围的结束日期不是月末。</span>
</p>


<h4 id="toc_9">(4)二进制数据类型</h4>

<p class="md_block">
    <span class="md_line">binary<br /></span>
    <span class="md_line">  存储固定大小的二进制个十数据，最多可存储8000字节。<br /></span>
    <span class="md_line">  这种数据类型主要用于存储作为标记或标记组合的数据。例如存储关于客户的标记。需要了解客户是否处于活动状态(值为1)、最近一个月有无消费记录(值为2)、最后一个月的消费额是否超过1000元(值为4)或者是否按时销账(值为8)。这将向数据库中加入4个数据列。然而，若使用binary值，如果客户有一个值为1101的二进制值，那么该客户拥有的值为1+4+8，这表明客户是活动的。最后一个月的消费额超过1000元并按时销账。<br /></span>
    <span class="md_line">varbinary<br /></span>
    <span class="md_line">  与binary十分相似，但是varbinary每一行的物理列大小随存储的值而不同。varbinary(max)能存储长度超过8000个字符的数据，最多可存储2GB，可用于存储类似图像这样的数据。</span>
</p>


<h4 id="toc_10">(5)专用数据类型</h4>

<p class="md_block">
    <span class="md_line">bit<br /></span>
    <span class="md_line">  该数据类型存储的值为0或1.通常用于判定真假值。<br /></span>
    <span class="md_line">uniqueidentifier<br /></span>
    <span class="md_line">  用于存储16位全局唯一标识符(UUID)。<br /></span>
    <span class="md_line">XML</span>
</p>


<h3 id="toc_11">②程序中的数据类型</h3>

<h4 id="toc_12">(1)cursor</h4>

<p class="md_block">
    <span class="md_line">  数据能够以驻留内存的状态进行存储。游标，与表类似，有数据行和列，但它们的相似之处仅限于此。不同之处如：游标没有索引。通过使用游标来建立数据集，以便一次处理一行数据。</span>
</p>


<h4 id="toc_13">(2)table</h4>

<p class="md_block">
    <span class="md_line">  table数据类型与游标和表有几分相似之处。该数据类型用于存储行和列的数据，但不能在数据上建索引。此时，系统可以“一次处理一个数据集”的数据，就想处理一个标准的表那样。</span>
</p>


<h4 id="toc_14">　(3)sql_varint</h4>

<p class="md_block">
    <span class="md_line">  可以根据存储的数据改变数据类型，即用来存储一些不同类型的数据类型。不过强烈不推荐使用这种数据类型。</span>
</p>


<h2 id="toc_15">3、表达式</h2>

<p class="md_block">
    <span class="md_line">表达式常指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子。</span>
</p>


<h3 id="toc_16">1.变量</h3>

<p class="md_block">
    <span class="md_line">  T-SQL的变量分为局部变量和全局变量。</span>
</p>


<h4 id="toc_17">(1)局部变量</h4>

<p class="md_block">
    <span class="md_line">  局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。<br /></span>
    <span class="md_line">  局部变量必须先声明，后使用。T-SQL还为局部变量提供了赋值语句。<br /></span>
    <span class="md_line">  ①declare变量声明语句，其语法格式为：</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="err">变量</span><span class="mi">1</span> <span class="p">[</span><span class="k">as</span><span class="p">]</span> <span class="n">datatype</span><span class="p">,</span><span class="o">@</span><span class="err">变量</span><span class="mi">2</span> <span class="p">[</span><span class="k">as</span><span class="p">]</span> <span class="n">datatype</span><span class="p">...</span>
</pre></div>

<!--block_code_end-->
<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">局部变量名称必须以@开始开头<br /></span>
    <span class="md_line">as可以省略<br /></span>
    <span class="md_line">赋初值NULL<br /></span>
    <span class="md_line">局部变量的赋值有三种方式：</span>
</p>


<ol>
<li>在变量定义的时候对其赋值：</li>
</ol>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="err">变量</span><span class="mi">1</span> <span class="p">[</span><span class="k">as</span><span class="p">]</span> <span class="n">datatype</span> <span class="o">=</span> <span class="n">value</span><span class="p">,</span><span class="o">@</span><span class="err">变量</span><span class="mi">2</span> <span class="p">[</span><span class="k">as</span><span class="p">]</span> <span class="n">datatype</span> <span class="o">=</span> <span class="n">value</span><span class="p">...</span>
</pre></div>

<!--block_code_end-->
<ol>
<li>select赋值语句，其语法格式为：</li>
</ol>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">select</span> <span class="o">@</span><span class="err">变量</span><span class="mi">1</span> <span class="o">=</span> <span class="err">表达式</span><span class="mi">1</span><span class="p">,</span><span class="o">@</span><span class="err">变量</span><span class="mi">2</span> <span class="o">=</span> <span class="err">表达式</span><span class="mi">2</span><span class="p">...</span>
</pre></div>

<!--block_code_end-->
<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">用select命令可以一次给多个变量赋值<br /></span>
    <span class="md_line">表达式可以为普通的value，也可以为查询结果<br /></span>
    <span class="md_line">当表达式为表的列名时，形式与普通查询中使用列别名的用法类似。可以使用子查询从表中一次返回多个值。如果查询的结果为多行，则只会把最后一行的相应列值赋给变量，这与PL/SQL的处理方式不同，在PL/SQL中，不允许把多行查询结果赋值给变量</span>
</p>


<ol>
<li>set赋值语句，其语法格式为：</li>
</ol>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">set</span> <span class="o">@</span><span class="err">变量</span> <span class="o">=</span> <span class="err">表达式</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">基本用法和select一样，区别在于一条set赋值语句只能给一个变量赋值，而一条select语句可以给多个变量赋值<br /></span>
    <span class="md_line">【示例】</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="n">sumsal</span> <span class="k">as</span> <span class="nb">numeric</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="mi">2</span><span class="p">),</span><span class="o">@</span><span class="n">dno</span> <span class="k">as</span> <span class="n">tinyint</span>
<span class="k">select</span> <span class="o">@</span><span class="n">dno</span> <span class="o">=</span> <span class="n">deptno</span><span class="p">,</span><span class="o">@</span><span class="n">sumsal</span> <span class="o">=</span> <span class="k">sum</span><span class="p">(</span><span class="n">sal</span><span class="p">)</span> 
<span class="k">from</span> <span class="n">emp</span> 
<span class="k">where</span> <span class="n">deptno</span> <span class="o">=</span> <span class="mi">10</span>
<span class="k">group</span> <span class="k">by</span> <span class="n">deptno</span>
<span class="n">print</span> <span class="k">cast</span><span class="p">(</span><span class="o">@</span><span class="n">dno</span> <span class="k">as</span> <span class="nb">varchar</span><span class="p">)</span><span class="o">+</span><span class="s1">&#39;：&#39;</span><span class="o">+</span><span class="k">cast</span><span class="p">(</span><span class="o">@</span><span class="n">sumsal</span> <span class="k">as</span> <span class="nb">varchar</span><span class="p">)</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_18">(2)全局变量</h4>

<p class="md_block">
    <span class="md_line">  全局变量由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。可以通过访问全局变量来了解系统目前的一些状态信息。<br /></span>
    <span class="md_line">  全局变量名以@@开头。下面给出一些常用的全局变量。</span>
</p>

<table>
 <thead><tr><th style="text-align:left">全局变量    说明</th>
<th style="text-align:left"></th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">@@error </td>
<td style="text-align:left">   上一条SQL语句报告的错误号</td>
</tr>
<tr><td style="text-align:left">@@nestlevel </td>
<td style="text-align:left">   当前存储过程或触发器的嵌套级别</td>
</tr>
<tr><td style="text-align:left">@@rowcount </td>
<td style="text-align:left">    上一条SQL语句处理的行数</td>
</tr>
<tr><td style="text-align:left">@@servername </td>
<td style="text-align:left">  本地服务器名称</td>
</tr>
<tr><td style="text-align:left">@@identity   </td>
<td style="text-align:left">最后插入的标识值</td>
</tr>
<tr><td style="text-align:left">@@spid </td>
<td style="text-align:left">    当前用户进程的会话id</td>
</tr>
<tr><td style="text-align:left">@@fetch_status </td>
<td style="text-align:left"> 上一条游标fetch语句的状态</td>
</tr>
<tr><td style="text-align:left">@@cpu_busy </td>
<td style="text-align:left"> SQL Server自上次启动后的时间状态</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_19">2.函数</h3>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">  函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句，处理结果成为“返回值”，处理过程成为“函数体”。<br /></span>
    <span class="md_line">  函数又分为系统内置函数和用户自动以函数。SQL Server提供了大量系统内置函数，主要可以分为以下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数。</span>
</p>


<ol>
<li>数学函数</li>
</ol>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">T-SQL中提供的常用的数学函数如下：<br /></span>
    <span class="md_line">abs()：返回绝对值<br /></span>
    <span class="md_line">round(数值表达式,长度,[,类型])：舍入到指定长度或精度。类型为0，表示舍入，类型为非0,表示截断<br /></span>
    <span class="md_line">power(m,n)：返回m的n次幂<br /></span>
    <span class="md_line">trunc()：将数字截断到指定的位数<br /></span>
    <span class="md_line">%：求余数，SQL Server没有mod(m,n)，而用m%n代替</span>
</p>


<ol>
<li>字符串函数</li>
</ol>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">ltrim(str,substr)/rtrim(str,substr)：str表示要操作的字符串，substr表示要裁剪的子串，若裁剪空格，则可以省略<br /></span>
    <span class="md_line">substring(str,position,length)：求子字符串<br /></span>
    <span class="md_line">replace(str,search_str,rep_str)：替换一个字符串中的子串。search_str表示要搜索的子字符串，rep_str表示要替换的目标字符串<br /></span>
    <span class="md_line">left(str,n)：返回字符串从左边开始的指定个数的字符<br /></span>
    <span class="md_line">len()：求字符串长度</span>
</p>


<ol>
<li>日期和时间函数</li>
</ol>

<p class="md_block">
    <span class="md_line">首先列出日期时间函数中的重要参数。</span>
</p>

<table>
 <thead><tr><th style="text-align:left">日期时间元素</th>
<th style="text-align:left">缩写</th>
<th style="text-align:left">含义</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">year </td>
<td style="text-align:left">  yy,yyyy </td>
<td style="text-align:left">   年</td>
</tr>
<tr><td style="text-align:left">month </td>
<td style="text-align:left"> m,mm </td>
<td style="text-align:left">  月</td>
</tr>
<tr><td style="text-align:left">day </td>
<td style="text-align:left">   d,dd </td>
<td style="text-align:left">  日</td>
</tr>
<tr><td style="text-align:left">dayofyear </td>
<td style="text-align:left"> dy </td>
<td style="text-align:left">    年的天数</td>
</tr>
<tr><td style="text-align:left">week </td>
<td style="text-align:left">  wk </td>
<td style="text-align:left">    星期数</td>
</tr>
<tr><td style="text-align:left">weekday </td>
<td style="text-align:left">   dw </td>
<td style="text-align:left">    星期几</td>
</tr>
<tr><td style="text-align:left">hour </td>
<td style="text-align:left">  hh </td>
<td style="text-align:left">    时</td>
</tr>
<tr><td style="text-align:left">minute </td>
<td style="text-align:left">    mi </td>
<td style="text-align:left">    分</td>
</tr>
<tr><td style="text-align:left">quarter </td>
<td style="text-align:left">   qq </td>
<td style="text-align:left">    刻</td>
</tr>
<tr><td style="text-align:left">second </td>
<td style="text-align:left">    ss </td>
<td style="text-align:left">    秒</td>
</tr>
<tr><td style="text-align:left">millisecond </td>
<td style="text-align:left">   ms </td>
<td style="text-align:left">    毫秒</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<p class="md_block">
    <span class="md_line">T-SQL中提供下列日期函数：<br /></span>
    <span class="md_line">getdate()：返回当前的日期和时间<br /></span>
    <span class="md_line">year(日期)：返回指定日期的“年”部分的整数<br /></span>
    <span class="md_line">month(日期)：返回指定日期的“月”部分的整数<br /></span>
    <span class="md_line">day(日期)：返回指定日期的“日”部分的整数<br /></span>
    <span class="md_line">datepart(日期元素，日期)：返回日期元素指定的日期部分的整数<br /></span>
    <span class="md_line">datename(日期元素，日期)：以字符串的形式返回日期元素指定时间的日期名称<br /></span>
    <span class="md_line">datediff(日期元素，日期1，日期2)：返回两个日期间的差值并将其转换为指定日期元素的形式<br /></span>
    <span class="md_line">dateadd(日期元素，数值，日期) ：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期<br /></span>
    <span class="md_line">涉及日期时间常量时，SQL Server建议使用与dateformat及语言环境设置无关的字符串格式，通常这样的字符串常量符合下面两种形式：</span>
</p>


<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">日期之间不使用分隔符，格式为yyyymmdd[ hh:mi:[:ss][,mmm]]，如&#39;20070703&#39;，&#39;20070703 17:53:00.997&#39;。<br /></span>
    <span class="md_line">ISO 8601标准形式，格式为yyyy-mm-ddThh:mi:ss[.mmm]，日期各个部分之间使用“-”分隔符，日期和时间部分用T分隔，并且时间部分不能省略，如&#39;2007-07-03T17:53:10&#39;。</span>
</p>


<ol>
<li>数据类型转换函数</li>
</ol>

<p class="md_block">
    <span class="md_line">  转换的方式有隐式转换和显式转换两种。<br /></span>
    <span class="md_line">  隐式转换是SQL Server自动地将数据从一种数据类型转换为另一种数据类型，用户不可见。<br /></span>
    <span class="md_line">  显式转换使用convert函数，该函数可以将一种数据类型的表达式强制转换为另一种数据类型的表达式。两种数据类型必须能够进行转换，例如，char值可以转换为binary，但不能转换为image。该函数的主要作用是把数值型或日期型数据转换为字符串，而只包含数字的字符串转换为数值型数据一般隐式转换。<br /></span>
    <span class="md_line">  格式：convert(数据类型(长度),表达式[,n])<br /></span>
    <span class="md_line">  函数的第4个参数n是可选的，用于日期时间型数据类型和字符数据类型转换。参数取值如下表所示。</span>
</p>

<table>
 <thead><tr><th style="text-align:left">不带世纪数位</th>
<th style="text-align:left">带世纪数位</th>
<th style="text-align:left">格式</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">1 </td>
<td style="text-align:left"> 101 </td>
<td style="text-align:left">   mm/dd/yyyy</td>
</tr>
<tr><td style="text-align:left">2 </td>
<td style="text-align:left"> 102 </td>
<td style="text-align:left">   yy.mm.dd</td>
</tr>
<tr><td style="text-align:left">3 </td>
<td style="text-align:left"> 103 </td>
<td style="text-align:left">   dd/mm/yyyy</td>
</tr>
<tr><td style="text-align:left">4 </td>
<td style="text-align:left"> 104 </td>
<td style="text-align:left">   dd.mm.yy</td>
</tr>
<tr><td style="text-align:left">5 </td>
<td style="text-align:left"> 105 </td>
<td style="text-align:left">   dd-mm-yy</td>
</tr>
<tr><td style="text-align:left">8 </td>
<td style="text-align:left"> 108 </td>
<td style="text-align:left">   hh:mi:ss</td>
</tr>
<tr><td style="text-align:left">- </td>
<td style="text-align:left"> 20或120 </td>
<td style="text-align:left">    yyyy-mm-dd hh:mi::ss(24h)</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<p class="md_block">
    <span class="md_line">【示例】</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">select</span> <span class="n">ename</span><span class="o">+</span><span class="s1">&#39;&#39;&#39;&#39;</span><span class="o">+</span><span class="s1">&#39;s sal is&#39;</span><span class="o">+</span><span class="k">convert</span><span class="p">(</span><span class="nb">char</span><span class="p">(</span><span class="mi">7</span><span class="p">),</span><span class="n">sal</span><span class="p">)</span> <span class="k">as</span> <span class="s1">&#39;EMPLOYEE&#39;</span><span class="n">S</span> <span class="n">SAL</span><span class="s1">&#39; from emp;</span>

<span class="s1">select cid as &#39;</span><span class="err">客户</span><span class="n">ID</span><span class="s1">&#39;,cname as &#39;</span><span class="err">客户名称</span><span class="s1">&#39;  convert(char(10),cRegisterationDate,102) as &#39;</span><span class="err">注册日期&#39;</span> <span class="k">from</span> <span class="n">customer</span><span class="p">;</span>
<span class="c1">-- 最终显示的日期格式就会如2006.12.02</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_20">3.运算符</h3>

<p class="md_block">
    <span class="md_line">算术运算符：+、-、*、/、%(求余)<br /></span>
    <span class="md_line">字符串运算符：+(连接)<br /></span>
    <span class="md_line">比较运算符：=、&gt;、&gt;=、&lt;、&lt;=、&lt;&gt;(不等于)、!&gt;(不大于)、!&lt;(不小于)<br /></span>
    <span class="md_line">逻辑运算符：NOT、AND、OR、ALL(所有)、ANY(或SOME，任意一个)、BETWEEN...AND、EXISTS(存在)、IN(在范围内)、LIKE(匹配)<br /></span>
    <span class="md_line">按位运算符：&amp;(位与)、|(位或)、^(按位异或)<br /></span>
    <span class="md_line">一元运算符：+(正)、-(负)、~(按位取反)<br /></span>
    <span class="md_line">赋值运算符：=(等于)</span>
</p>


<h3 id="toc_21">4.流程控制语句</h3>

<h4 id="toc_22">1.程序块语句BEGIN...END</h4>

<p class="md_block">
    <span class="md_line">程序块语句用于将多条T-SQL语句封装起来构成一个程序块。SQLServer在处理时，将整个程序块视为一条T-SQL语句执行。<br /></span>
    <span class="md_line">经常与while或if...else组合起来使用，可以相互嵌套。</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">begin</span>
<span class="err">　　</span><span class="o">&lt;</span><span class="n">T</span><span class="o">-</span><span class="k">SQL</span><span class="err">命令行或程序块</span><span class="o">&gt;</span>
<span class="k">end</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_23">2.判断语句IF...ELSE</h4>

<p class="md_block">
    <span class="md_line">if...else语句用于条件测试，系统将根据条件满足与否来决定如何执行语句，else子句是可选的。<br /></span>
    <span class="md_line">if的形式通常包括if exists(用于判断是否存在)和if not(是否条件不满足)</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">if</span> <span class="err">逻辑表达式　　　　</span>
    <span class="err">语句块</span><span class="mi">1</span>
<span class="k">else</span>
    <span class="err">语句块</span><span class="mi">2</span>
<span class="err">语句块</span><span class="mi">3</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">示例</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">DECLARE</span> <span class="o">@</span><span class="n">i</span> <span class="nb">int</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="o">&lt;</span> <span class="mi">5</span><span class="p">)</span>
    <span class="n">PRINT</span> <span class="s1">&#39;小于5&#39;</span><span class="p">;</span>
<span class="k">ELSE</span> <span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="o">&lt;</span> <span class="mi">8</span><span class="p">)</span>
    <span class="k">BEGIN</span>
        <span class="n">PRINT</span> <span class="s1">&#39;小于8&#39;</span>
    <span class="k">END</span>
<span class="k">ELSE</span>
    <span class="k">BEGIN</span>
        <span class="n">PRINT</span> <span class="s1">&#39;前面都不满足!&#39;</span>
    <span class="k">END</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_24">3.循环语句WHILE</h4>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">while语句用于执行循环，可以根据循环条件重复执行语句块。通常使用break和continue关键字在循环内部进行控制。</span>
</p>


<ol>
<li>break语句让程序跳出循环体，结束while的循环。</li>
<li>continue语句让程序跳过[sql语句块3]，回到while&lt;条件表达式&gt;，重新判断逻辑值执行。</li>
<li>where语句可以互相嵌套。</li>
</ol>

<p class="md_block">
    <span class="md_line">语法:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="n">while</span><span class="o">&lt;</span><span class="err">条件表达式</span><span class="o">&gt;</span>
    <span class="o">&lt;</span><span class="k">sql</span><span class="err">语句块</span><span class="mi">1</span><span class="o">&gt;</span>
    <span class="p">[</span><span class="n">break</span><span class="p">]</span>
    <span class="o">&lt;</span><span class="k">sql</span><span class="err">语句块</span><span class="mi">2</span><span class="o">&gt;</span>
    <span class="p">[</span><span class="k">continue</span><span class="p">]</span>
    <span class="o">&lt;</span><span class="k">sql</span><span class="err">语句块</span><span class="mi">3</span><span class="o">&gt;</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">示例:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">DECLARE</span> <span class="o">@</span><span class="n">i</span> <span class="nb">int</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span>
<span class="n">WHILE</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="o">&lt;</span> <span class="mi">10</span><span class="p">)</span>
<span class="k">BEGIN</span>
    <span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="o">@</span><span class="n">i</span> <span class="o">+</span> <span class="mi">1</span><span class="p">;</span>
    <span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="o">%</span> <span class="mi">2</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span>
    <span class="k">BEGIN</span>
        <span class="n">PRINT</span><span class="p">(</span><span class="s1">&#39;跳过2的倍数&#39;</span> <span class="o">+</span> <span class="k">CAST</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="k">AS</span> <span class="nb">varchar</span><span class="p">));</span>
        <span class="k">CONTINUE</span><span class="p">;</span>
    <span class="k">END</span>
    <span class="k">ELSE</span> <span class="k">IF</span> <span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">7</span><span class="p">)</span>
    <span class="k">BEGIN</span>
        <span class="n">PRINT</span><span class="p">(</span><span class="s1">&#39;到&#39;</span> <span class="o">+</span> <span class="k">CAST</span><span class="p">(</span><span class="o">@</span><span class="n">i</span> <span class="k">AS</span> <span class="nb">varchar</span><span class="p">)</span> <span class="o">+</span> <span class="s1">&#39;就跳出循环&#39;</span><span class="p">);</span>
        <span class="n">BREAK</span><span class="p">;</span>
    <span class="k">END</span>
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">i</span><span class="p">;</span>
<span class="k">END</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">以上代码输出如下结果</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="mi">1</span>
<span class="err">跳过</span><span class="mi">2</span><span class="err">的倍数</span><span class="mi">2</span>
<span class="mi">3</span>
<span class="err">跳过</span><span class="mi">2</span><span class="err">的倍数</span><span class="mi">4</span>
<span class="mi">5</span>
<span class="err">跳过</span><span class="mi">2</span><span class="err">的倍数</span><span class="mi">6</span>
<span class="err">到</span><span class="mi">7</span><span class="err">就跳出循环</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_25">4、分支判断语句CASE</h4>

<p class="md_block">
    <span class="md_line">CASE语句用于执行多条件的分支判断。<br /></span>
    <span class="md_line">语法:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CASE</span> <span class="n">input_expression</span>
    <span class="k">WHEN</span> <span class="n">when_expression</span>
    <span class="k">THEN</span> <span class="n">result_expression</span>
    <span class="p">[...</span><span class="n">n</span><span class="p">]</span>
    <span class="p">[</span>
    <span class="k">ELSE</span> <span class="n">else_result_expression</span>
    <span class="p">]</span>
    <span class="k">END</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">实例:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 一张表只有Id，Sex两个字段，要求用一条SQL语句将Sex字段的&#39;1&#39;变&#39;男&#39;，&#39;0&#39;变&#39;女&#39;。</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="k">case</span> <span class="k">when</span> <span class="n">sex</span><span class="o">=</span><span class="s1">&#39;1&#39;</span> <span class="k">then</span> <span class="s1">&#39;男&#39;</span> <span class="k">when</span> <span class="n">sex</span><span class="o">=</span><span class="s1">&#39;0&#39;</span> <span class="k">then</span> <span class="s1">&#39;女&#39;</span> <span class="k">else</span> <span class="s1">&#39;泰国人&#39;</span> <span class="k">end</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>
<span class="c1">-- 再来一个有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。</span>
<span class="k">select</span> <span class="n">Id</span><span class="p">,</span>
    <span class="p">(</span><span class="k">case</span> 
            <span class="k">when</span> <span class="n">chinese</span> <span class="o">&gt;=</span> <span class="mi">80</span> <span class="k">then</span> <span class="s1">&#39;优秀&#39;</span> 
            <span class="k">when</span> <span class="n">chinese</span> <span class="o">&gt;=</span> <span class="mi">60</span> <span class="k">then</span> <span class="s1">&#39;及格&#39;</span> 
            <span class="k">else</span>  <span class="s1">&#39;不及格&#39;</span> 
    <span class="k">end</span><span class="p">)</span> <span class="k">as</span> <span class="err">语文</span><span class="p">,</span>
    <span class="p">(</span><span class="k">case</span> 
        <span class="k">when</span> <span class="n">math</span> <span class="o">&gt;=</span> <span class="mi">80</span> <span class="k">then</span> <span class="s1">&#39;优秀&#39;</span> 
        <span class="k">when</span> <span class="n">math</span> <span class="o">&gt;=</span> <span class="mi">60</span> <span class="k">then</span> <span class="s1">&#39;及格&#39;</span> 
        <span class="k">else</span> <span class="s1">&#39;不及格&#39;</span> 
    <span class="k">end</span><span class="p">)</span> <span class="k">as</span> <span class="err">数学</span><span class="p">,</span>
        <span class="p">(</span><span class="k">case</span> 
        <span class="k">when</span> <span class="n">english</span> <span class="o">&gt;=</span> <span class="mi">80</span> <span class="k">then</span> <span class="s1">&#39;优秀&#39;</span> 
        <span class="k">when</span> <span class="n">english</span> <span class="o">&gt;=</span> <span class="mi">60</span> <span class="k">then</span> <span class="s1">&#39;及格&#39;</span> 
        <span class="k">else</span> <span class="s1">&#39;不及格&#39;</span> 
    <span class="k">end</span><span class="p">)</span> <span class="k">as</span> <span class="err">英语</span>
<span class="k">from</span> <span class="n">fenshu</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_26">5.无条件退出语句RETURN</h4>

<p class="md_block">
    <span class="md_line">RETURN语句用于使程序从一个查询、存储过程或批量处理中无条件返回，其后面的语句不再执行。如果在存储过程中使用return语句，那么此语句可以指定返回给调用应用程序、批处理或过程的整数；如果没有为return指定整数值，那么该存储过程将返回0。<br /></span>
    <span class="md_line">存储过程返回值：</span>
</p>

<table>
 <thead><tr><th style="text-align:left">返回值 含义</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">0   存储过程执行成功</td>
</tr>
<tr><td style="text-align:left">-1  没有找到数据库对象</td>
</tr>
<tr><td style="text-align:left">-2  数据类型错误</td>
</tr>
<tr><td style="text-align:left">-3  进程死锁错误</td>
</tr>
<tr><td style="text-align:left">-4  进程死锁错误</td>
</tr>
<tr><td style="text-align:left">-5  语法错误</td>
</tr>
<tr><td style="text-align:left">-6  其他用户错误</td>
</tr>
<tr><td style="text-align:left">-7  资源错误</td>
</tr>
<tr><td style="text-align:left">-8  非致命的内部错误</td>
</tr>
<tr><td style="text-align:left">-9  达到系统配置参数极限</td>
</tr>
<tr><td style="text-align:left">-10 内部一致性致命错误</td>
</tr>
<tr><td style="text-align:left">-11 内部一致性致命错误</td>
</tr>
<tr><td style="text-align:left">-12 表或索引崩溃</td>
</tr>
<tr><td style="text-align:left">-13 数据库崩溃</td>
</tr>
<tr><td style="text-align:left">-14 硬件错误</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<p class="md_block">
    <span class="md_line">语法：</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">return</span> <span class="p">[</span><span class="err">整数表达式</span><span class="p">]</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">示例：</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">BEGIN</span>
    <span class="n">PRINT</span><span class="p">(</span><span class="mi">1</span><span class="p">);</span>
    <span class="n">PRINT</span><span class="p">(</span><span class="mi">2</span><span class="p">);</span>
    <span class="k">RETURN</span><span class="p">;</span>
    <span class="n">PRINT</span><span class="p">(</span><span class="mi">3</span><span class="p">);</span>    <span class="c1">--在RETURN之后的代码不会被执行，因为会跳过当前批处理</span>
<span class="k">END</span>
<span class="k">GO</span>
<span class="k">BEGIN</span>
    <span class="n">PRINT</span><span class="p">(</span><span class="mi">4</span><span class="p">);</span>
<span class="k">END</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_27">6.无条件跳转语句GOTO</h4>

<p class="md_block">
    <span class="md_line">GOTO语句可以使程序无条件跳转到指定的程序执行点，增加了程序设计的灵活性。但破坏了程序的结构化，使程序结构变得复杂而且难以测试。<br /></span>
    <span class="md_line">语法:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">GOTO</span> <span class="err">语句标识符</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">使用说明：<br /></span>
    <span class="md_line">语句标识符可以是数字或者字母的组合，但必须以&quot;:&quot;结束。而在GOTO语句后的标识符不必带&quot;:&quot;。<br /></span>
    <span class="md_line">注意事项：<br /></span>
    <span class="md_line">GOTO语句和跳转标签可以在存储过程、批处理或语句块中的任何地方使用，但不能超出批处理的范围。<br /></span>
    <span class="md_line">示例:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">DECLARE</span> <span class="o">@</span><span class="n">i</span> <span class="nb">int</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">4</span><span class="p">;</span>
<span class="k">GOTO</span> <span class="n">ME</span><span class="p">;</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>  <span class="c1">--这行被跳过了</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">6</span><span class="p">;</span> <span class="c1">--这行被跳过了</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">i</span> <span class="o">=</span> <span class="mi">7</span><span class="p">;</span> <span class="c1">--这行被跳过了</span>
<span class="n">ME</span><span class="p">:</span><span class="n">PRINT</span><span class="p">(</span><span class="s1">&#39;跳到我了?&#39;</span><span class="p">);</span>
<span class="n">PRINT</span> <span class="o">@</span><span class="n">i</span>
</pre></div>

<!--block_code_end-->
<h4 id="toc_28">7.延期执行语句WAITFOR　　</h4>

<p class="md_block">
    <span class="md_line">waitfor语句用于挂起语句的执行，直到指定的时间点或者指定的时间间隔。<br /></span>
    <span class="md_line">1、指定时间点的语法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="n">waitfor</span>
<span class="err">{</span>
    <span class="n">Time</span> <span class="s1">&#39;time&#39;</span>
<span class="err">}</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">示例:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="n">WAITFOR</span> <span class="n">DELAY</span> <span class="s1">&#39;00:00:03&#39;</span>
    <span class="k">BEGIN</span> 
    <span class="n">PRINT</span> <span class="s1">&#39;延迟3秒输出!&#39;</span><span class="p">;</span>
<span class="k">END</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">2、指定等待时间间隔的语法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="n">waitfor</span>
<span class="err">{</span>
    <span class="n">delay</span> <span class="s1">&#39;interval&#39;</span>
<span class="err">}</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">interval为时间间隔，指定执行waitfor语句之前需要等待的时间，最多为24小时。<br /></span>
    <span class="md_line">waitfor语句常用语某个特定的时间点或时间间隔自动执行某些任务。在waitfor语句中不能包含打开游标，定义视图这样的操作。在包含事务的语句中不要使用waitfor语句，因为waitfor语句在时间点或时间间隔执行期间将一直拥有对象的锁，当事务中包含waitfor语句，事务的其他语句又需要访问被锁住的数据对象事就容易发生死锁现象。<br /></span>
    <span class="md_line">示例：对着始终来看真是分毫不差啊。</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="n">WAITFOR</span> <span class="n">TIME</span> <span class="s1">&#39;15:49:22&#39;</span>
<span class="k">BEGIN</span> 
    <span class="n">PRINT</span> <span class="s1">&#39;定时输出&#39;</span>
<span class="k">END</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>